-- created by kumiler
-- on 2022/6/30 14:52
-- DESC
-- show create table jms_dm.dm_waybill_customer_complaint_agg_new_dt;
--alter table jms_dm.dm_waybill_customer_complaint_agg_new_dt set tblproperties ('external.table.purge' = 'true');
--drop table jms_dm.dm_waybill_customer_complaint_agg_new_dt;
CREATE EXTERNAL TABLE jms_dm.dm_waybill_customer_complaint_agg_new_dt
(
    regional_code              string COMMENT '大区编码',
    regional_desc              string COMMENT '大区名称',
    proxy_code                 string COMMENT '代理区编码',
    proxy_name                 string COMMENT '代理区名称',
    work_cnt                   bigint COMMENT '呼叫中心客诉量',
    work_total                 bigint COMMENT '呼叫中心客诉量汇总',
    project_cnt                bigint COMMENT '平台客诉量',
    project_total              bigint COMMENT '平台客诉量汇总',
    appeal_cnt                 bigint COMMENT '邮政客诉量',
    appeal_total               bigint COMMENT '邮政客诉量汇总',
    claim_cnt                  bigint COMMENT '理赔客诉量',
    claim_total                bigint COMMENT '理赔客户量汇总',
    third_platform_cnt         bigint COMMENT '第三方客诉量',
    third_platform_ratio       decimal(10, 2) COMMENT '第三方客诉率',
    third_platform_total       bigint COMMENT '第三方客诉量汇总',
    third_platform_total_ratio decimal(10, 2) COMMENT '第三方汇总客诉率',
    transfer_cnt               bigint COMMENT '转运客诉量',
    transfer_cnt_ratio         decimal(10, 2) COMMENT '转运客诉率',
    transfer_total             bigint COMMENT '转运客诉量-全网',
    transfer_total_ratio       decimal(10, 2) COMMENT '转运客诉率-全网',
    network_cnt                bigint COMMENT '网点客诉量',
    network_total              bigint COMMENT '网点累计客诉量',
    network_ratio              decimal(10, 2) COMMENT '网点客诉率',
    network_total_ratio        decimal(10, 2) COMMENT '网点累计客诉率',
    all_cnt                    bigint COMMENT '全网客诉量',
    all_ratio                  decimal(10, 2) COMMENT '全网客诉率',
    all_total                  bigint COMMENT '全网客诉量汇总',
    all_total_ratio            decimal(10, 2) COMMENT '全网客诉率汇总',
    taking_cnt                 bigint COMMENT '代理区出件量',
    arrival_cnt                bigint COMMENT '代理区到件量',
    data_time                  string COMMENT '数据ETL日期'
)
    COMMENT '客诉量日汇总版本报表-日'
    PARTITIONED BY (dt string COMMENT '分区字段')
    stored as parquet
    LOCATION '/dw/hive/jms_dm/hive/jms_dm.db/external/dm_waybill_customer_complaint_agg_new_dt'
    TBLPROPERTIES (
        'bucketing_version' = '2',
        'discover.partitions' = 'true'
        );